﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Frame.Lib.Core.Base;
using Frame.Lib.Utilities;
using Frame.Lib.Data;
using Frame.Lib.Core;


namespace Jzy.DAL.Calendar
{
    public class CalendarDAL : BaseDAL
    {
        #region 获得公司所有人一月的日程
        /// <summary>
        /// 获得所公司有人一月的日程
        /// </summary>
        /// <param name="time"></param>
        /// <returns></returns>
        public DataTable GetList(DateTime time)
        {           
            string strSql = GetSql(time);
            DataTable dt = dbOper.Query(strSql);            
            return dt;
        }
        #endregion
      

        /// <summary>
        /// 根据时间获得当时一个月的所有日程的语句
        /// </summary>
        /// <param name="time"></param>
        /// <returns></returns>
        private string GetSql(DateTime time)
        {
            DateTime startTime = new DateTime(time.Year, time.Month, 1);
            DateTime endTime = startTime.AddMonths(1);

            startTime = startTime.AddDays(-7);//往前加7天
            endTime = endTime.AddDays(7);//往后加7天

            string strSql = "select ID,[Name],StartTime,isnull(EndTime,StartTime) as EndTime,CreatorID,CreatorName,JoinerNames " +
                            "from " + OTableName.Calendar + " where StartTime<='" + endTime.ToString() + "'  and isnull(EndTime,StartTime)>='" + startTime.ToString() + "' ";
            return strSql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="time"></param>
        /// <param name="userID">用户的ID</param>
        /// <param name="lstUpperDeptIDs">当前用户所属部门及所有上级部门</param>
        /// <returns></returns>
        public DataTable GetList(DateTime time, object userID, List<int> lstUpperDeptIDs)
        {
            string strSql = GetSql(time) +
                            "and ( ID in (select CalID from " + OTableName.CalUser + " where UserID = " + userID + ") or CreatorID = " + userID; //把创建人加进去

            if (lstUpperDeptIDs != null && lstUpperDeptIDs.Count > 0)
            {
                strSql += " or ID in (select CalID from " + OTableName.CalDept + " where DeptID in (" + ConvertUtil.ConvertToDbStr(lstUpperDeptIDs) + ")))";
            }
            else
            {
                strSql += ")";
            }

            DataTable dt = dbOper.Query(strSql);

            
            return dt;
        }

        /// <summary>
        /// 获得所有直接分配给本部门所有上级部门的日程和分配给下级部门和下级部门所有员工的日程
        /// </summary>
        /// <param name="time"></param>
        /// <param name="lstUpperDeptIDs">某一部门的所有上级部门</param>
        /// <param name="lstSubDeptIDs">某一部门的所有下级部门，包括自己</param>
        /// <returns></returns>
        public DataTable GetList(DateTime time, List<int> lstUpperDeptIDs, List<int> lstSubDeptIDs)
        {
            string strSql = GetSql(time);

            string condition = null;

            List<int> lstAllDeptIDs = new List<int>();//直接分配给上级一条线的日程和直接分配给下级单位的日程
            if (lstUpperDeptIDs != null)
            {
                lstAllDeptIDs.AddRange(lstUpperDeptIDs);
            }
            if (lstSubDeptIDs != null)
            {
                lstAllDeptIDs.AddRange(lstSubDeptIDs);
            }
            if (lstAllDeptIDs.Count > 0)
            {
                condition = " ID in (select CalID from " + OTableName.CalDept + " where DeptID in (" + ConvertUtil.ConvertToDbStr(lstAllDeptIDs) + "))";
            }

            if (lstSubDeptIDs != null && lstSubDeptIDs.Count > 0)//分配给下级单位所有员工的日程
            {
                if (condition != null)
                {
                    condition += " or ";
                }
                condition += "ID in (select CalID from " + OTableName.CalUser + " where UserID in (select UserID from " + STableName.DeptUser + " where DeptID in (" + ConvertUtil.ConvertToDbStr(lstSubDeptIDs) + ")))" +
                             " or CreatorID in (select UserID from " + STableName.DeptUser + " where DeptID in (" + ConvertUtil.ConvertToDbStr(lstSubDeptIDs) + "))";
            }
            if (condition != null)
            {
                strSql += " and (" + condition + ")";
            }
            DataTable dt = dbOper.Query(strSql);
            return dt;
        }


        #region 获得创建者ＩＤ
        /// <summary>
        /// 获得创建者ＩＤ
        /// </summary>
        /// <param name="strCalID"></param>
        /// <returns></returns>
        public static int GetCreatorID(int id)
        {
            string strSql = "select CreatorID from " + OTableName.Calendar + " where ID=" + id;
            int creatorID = DBHelper.Instance().Query<int>(strSql).First();
            return creatorID;
        }
        #endregion

        //public int GetTodayCalendarCount(int userID)
        //{
        //    string sql="select count(*) from Calendar where ID in ( 
        //}
    }
}
